using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using QuanLyThuVien1.DTO;
namespace QuanLyThuVien1.DAO
{
    public class CPHIEUMUON
    {
        public DataTable layDanhSachPM()
        {
            DataTable danhSach = new DataTable();
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from PHIEUMUON";
            OleDbDataAdapter oleDb = new OleDbDataAdapter(connecstr,connect);
            oleDb.Fill(danhSach);
            connect.Close();
            return danhSach;
        }
        public PHIEUMUON timKiemPM(string maPhieu)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            PHIEUMUON PM = new PHIEUMUON();
            string connecstr = "select * from PHIEUMUON where soPhieuMuon = " + maPhieu;
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            OleDbDataReader Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                PM.SOPHIEUMUON = Reader.GetString(0);
                PM.NGAYMUON = Reader.GetDateTime(1);
                PM.NGAYTRADUKIEN = Reader.GetDateTime(2);
                PM.MANV = Reader.GetString(3);
                PM.MADG = Reader.GetString(4);
            }
            connect.Close();
            Reader.Close();
            return PM;

        }
        public void themPM(PHIEUMUON PM)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "insert into PHIEUMUON values('" + PM.SOPHIEUMUON + "','" + PM.NGAYMUON + "','" + PM.NGAYTRADUKIEN + "',";
            connecstr += "'" + PM.MANV +"','" + PM.MADG + "')";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.ExecuteNonQuery();
            connect.Close();
        }
        public void xoaPM(string maPhieu)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "delete from PHIEUMUON where soPhieuMuon =  '"+ maPhieu+"'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.ExecuteNonQuery();
            connect.Close();
 
        }
        public DateTime LayNgayTra(string soPhieuMuon)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select ngayTraDukien from PHIEUMUON where soPhieuMuon = '"+soPhieuMuon+"'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            DataTable d = new DataTable();
            OleDbDataAdapter a = new OleDbDataAdapter(command);
            a.Fill(d);
            connect.Close();
            return DateTime.Parse(d.Rows[0][0].ToString());
        }
        public bool KiemTraPM(string maPhieu)
        {

            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from PHIEUMUON where soPhieuMuon = '" + maPhieu + "'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            DataTable d = new DataTable();
            OleDbDataAdapter a = new OleDbDataAdapter(command);
            a.Fill(d);
            connect.Close();
            if (d.Rows.Count > 0)
                return true;
            return false;
        }
        public int DemSoPhieuMuon(string maDG)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select count(*) from PHIEUMUON where ma_DG = '" + maDG + "'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            DataTable d = new DataTable();
            OleDbDataAdapter a = new OleDbDataAdapter(command);
            a.Fill(d);
            connect.Close();
            return int.Parse(d.Rows[0][0].ToString());
 
        }
        
    }
}
